﻿    /***************************************************************************************************
    **  Table:	    public.student
    **  Author:		Zhixian Ong
    **
    **  Description:
    **    
    **    Represents a registered course.
    **
    **  Modification History:
    **
    **      20140102  Zhixian Ong     - Initial data
    ** 
    **  Status:
    **      Dev
    ** 
    ***************************************************************************************************/

	----------------------------------------------------------------------------
	-- Insert
	
    WITH x AS
    (
        SELECT * FROM (VALUES 
            (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)
        ) AS t(n)
    )
    INSERT INTO unit (building_id, full_name, display_name)
    SELECT  x0.n                                                                        AS "block",
            '#' || CAST(x1.n AS VARCHAR) || CAST(x2.n AS VARCHAR) || '-' || '0' || x3.n AS "full_name",
            '#' || CAST(x1.n AS VARCHAR) || CAST(x2.n AS VARCHAR) || '-' || '0' || x3.n AS "display_name"
    FROM    x as x0, x as x1, x as x2, x as x3
    WHERE   (x1.n::varchar || x2.n::varchar)::int > 0 
            AND x0.n > 0    -- offset by 1; so that we start with block 1 instead of 0
            AND x0.n <= 3   -- number of blocks
            AND CAST((CAST(x1.n AS VARCHAR) || CAST(x2.n AS VARCHAR)) AS INT) <= 35   -- number of floor
            AND x3.n > 0    -- 
            AND x3.n <= 6   -- number of units per floor
    ORDER BY 
            x0.n,
            CAST((CAST(x1.n AS VARCHAR) || CAST(x2.n AS VARCHAR)) AS INT),
            x3.n;

    ----------------------------------------------------------------------------
	-- Select

    SELECT * FROM public.unit;
